********************************************************************************
** 	TITLE: e20_crime_figures.do

**	PROJECT: IGNITE
** 
**  PURPOSE: Appendix Figure XX: Criminal Offenses
********************************************************************************
**  PURPOSE: Crime figures for Genesee and Saginaw
********************************************************************************
		set sortseed 13

import excel "${input_data}/criminal_offense/annual_index_crimes_citytwps_2017.xlsx", cellrange(A1:N1609) firstrow clear
replace CountyandCityTownship = "Genesee" in 504
keep if inlist(CountyandCityTownship,"Genesee","Saginaw")

rename (CountyandCityTownship ViolentCrime PropertyCrime Arson IndexCrimes NonIndexCrimes TotalCrimes) (county violent_crime property_crime arson part_i_crime part_ii_crime total_crime)
rename *, lower
gen year=2017
tempfile crimes_type_2017 
save `crimes_type_2017'

import excel "${input_data}/criminal_offense/q_violent_property_crimes_2018.xlsx", cellrange(A1:N1598) firstrow clear
keep if inlist(CountyandCityTownship,"Genesee","Saginaw")

rename (CountyandCityTownship ViolentCrime PropertyCrime Arson PartICrimes PartIICrimes TotalCrimes) (county violent_crime property_crime arson part_i_crime part_ii_crime total_crime)
rename *, lower
gen year=2018
tempfile crimes_type_2018 
save `crimes_type_2018'

import excel "${input_data}/criminal_offense/violent_property_crimes_citytwp_2019.xlsx", cellrange(A1:N1583) firstrow clear
keep if inlist(CountyandCityTownship,"Genesee","Saginaw")

rename (CountyandCityTownship ViolentCrime PropertyCrime Arson PartICrimes PartIICrimes TotalCrimes) (county violent_crime property_crime arson part_i_crime part_ii_crime total_crime)
rename *, lower
gen year=2019
tempfile crimes_type_2019 
save `crimes_type_2019'

import excel "${input_data}/criminal_offense/violent_and_property_by_county_and_city_township_2020.xlsx", cellrange(A4:N1592) sheet("Part I and II Crimes") firstrow clear
keep if inlist(A,"Genesee","Saginaw")

rename (A AggravatedAssault ViolentTotal PropertyTotal K L M N) (county aggassaults violent_crime property_crime arson part_i_crime part_ii_crime total_crime)
rename *, lower
gen year=2020
tempfile crimes_type_2020 
save `crimes_type_2020'

import excel "${input_data}/criminal_offense/Violent Property by County and City_ 2021.xlsx", cellrange(A4:N1592)  sheet("Part I and II Crimes") firstrow clear
keep if inlist(A,"Genesee","Saginaw")

rename (A AggravatedAssault ViolentTotal PropertyTotal K L M N) (county aggassaults violent_crime property_crime arson part_i_crime part_ii_crime total_crime)
rename *, lower
gen year=2021
tempfile crimes_type_2021 
save `crimes_type_2021'

import excel "${input_data}/criminal_offense/2022 Violent and Property Crimes by County and City Township.xlsx", cellrange(A4:N1592) sheet("Index Crimes") firstrow clear
keep if inlist(A,"Genesee","Saginaw")

rename (A AggravatedAssault ViolentTotal PropertyTotal K L M N) (county aggassaults violent_crime property_crime arson part_i_crime part_ii_crime total_crime)
rename *, lower
gen year=2022
tempfile crimes_type_2022 
save `crimes_type_2022'

use `crimes_type_2017'
append using `crimes_type_2018'
append using `crimes_type_2019'
append using `crimes_type_2020'
append using `crimes_type_2021'
append using `crimes_type_2022'

reshape wide murder - total_crime, i(year) j(county) string

//Getting share of Genesee Population
foreach var of varlist murderGenesee -total_crimeGenesee {
	gen `var'_100 = (`var'/406211)*100000
	}

//Getting share of Saginaw Population
foreach var of varlist murderSaginaw -total_crimeSaginaw {
	gen `var'_100 = (`var'/190124)*100000
	}

	
twoway connected  violent_crimeGenesee_100 violent_crimeSaginaw_100 year, lcolor(ebblue cranberry) lpattern(solid solid) ///
legend(order(1 "Genesee" 2 "Saginaw") label(1 "Life expectancy") label(2 "Life expectancy") position(6) cols(2)) msymbol(D D) ///
ytitle("Violent Crime per 100,000 Population") xtitle("Month") msymbol(Dh Dh) mcolor(ebblue cranberry) graphregion(fcolor(white)) ///
bgcolor(white) yla(, nogrid) xla(, nogrid) xline(2020, lwidth(*2) lc(gs8) lp(solid)) ///
	ttext(3000 2019.7 "IGNITE", size(*.85))

gr export "$figures/violent_crimes_luciagll_nat.pdf", replace

twoway connected property_crimeGenesee_100 property_crimeSaginaw_100 year, lcolor(ebblue cranberry) lpattern(solid solid) ///
legend(order(1 "Genesee" 2 "Saginaw") label(1 "Life expectancy") label(2 "Life expectancy") position(6) cols(2)) msymbol(D D) ///
ytitle("Property Crimes per 100,000 Population") xtitle("Month") msymbol(Dh Dh) mcolor(ebblue cranberry) graphregion(fcolor(white)) ///
bgcolor(white) yla(, nogrid) xla(, nogrid) xline(2020, lwidth(*2) lc(gs8) lp(solid)) ///
	ttext(3000 2019.7 "IGNITE", size(*.85))

gr export "$figures/property_crimes_luciagll_nat.pdf", replace

twoway connected part_i_crimeGenesee_100 part_i_crimeSaginaw_100 year, lcolor(ebblue cranberry) lpattern(solid solid) ///
legend(order(1 "Genesee" 2 "Saginaw") label(1 "Life expectancy") label(2 "Life expectancy") position(6) cols(2)) msymbol(D D) ///
ytitle("Total Part I Crimes per 100,000 Population") xtitle("Month") msymbol(Dh Dh) mcolor(ebblue cranberry) graphregion(fcolor(white)) ///
bgcolor(white) yla(, nogrid) xla(, nogrid) xline(2020, lwidth(*2) lc(gs8) lp(solid)) ///
	ttext(3000 2019.7 "IGNITE", size(*.85))

gr export "$figures/part_i_crimes_luciagll_nat.pdf", replace

twoway connected total_crimeGenesee_100 total_crimeSaginaw_100 year, lcolor(ebblue cranberry) lpattern(solid solid) ///
legend(order(1 "Genesee" 2 "Saginaw") label(1 "Life expectancy") label(2 "Life expectancy") position(6) cols(2)) msymbol(D D) ///
ytitle("Total Crimes per 100,000 Population") xtitle("Month") msymbol(Dh Dh) mcolor(ebblue cranberry) graphregion(fcolor(white)) ///
bgcolor(white) yla(, nogrid) xla(, nogrid) xline(2020, lwidth(*2) lc(gs8) lp(solid)) ///
	ttext(10000 2019.7 "IGNITE", size(*.85))

gr export "$figures/total_crimes_luciagll_nat.pdf", replace